*************************************************
*			03_eia923_pricing					*
*												*
*	Clean average prices data from EIA923		*
*												*
*	Raw: 	EIA 923 							*
*	Output: 923_resale.dta			*
*												*
*************************************************

* Run 00_data_prep to define $last_year

* Program to clean variable names

cap program drop fix_id_vars
program define fix_id_vars
  qui{
  
    cap rename *, lower
	forval i=1/5{
	cap rename *_* **
    }
	
    
	cap keep year plantcode grossgeneration *sale* *revenue*
	
	*rename
	cap ren plantcode facilityid
    cap ren grossgeneration grossgen 
	cap ren retailsales retail_sales
	cap ren salesforresale wholesale_sales
	cap ren revenue* wholesale_revenue
  
  }
end

forval i = 2011/$last_year {

  
  if inlist(`i',2013,2018,2021){
    local file "eia_f923/f923_`i'/EIA923_Schedules_6_7_NU_SourceNDisposition_`i'_Final.xlsx"
	local sheet "Source_and_disposition"
  }
  
  else {
    local file "eia_f923/f923_`i'/EIA923_Schedules_6_7_NU_SourceNDisposition_`i'_Final_Revision.xlsx"
  }
  
  if inlist(`i',2011,2013){
    local sheet "Source_and_disposition, `i'"
  }
  else {
	local sheet  "Source_and_disposition"
  }  
	  
 
  

  
  ***
  * Import each year's data
  ***  

  import excel "${wind}/data_raw/`file'", ///
		sheet("`sheet'") firstrow cellrange(A5) clear
	
  * Clean variables	
  fix_id_vars
  qui destring year facilityid grossgen *sales *revenue, replace
  
  * Save
  tempfile f923`i'
  save "`f923`i''"
  di "`i'"
  }
  
  
***
* Append all yearly revenue data
***    

clear
forval i = 2011/$last_year  {
	append using "`f923`i''"
}

* Calculate average wholesale price ($1000/Mwh)
gen av_wholesale_price = wholesale_revenue/wholesale_sales if wholesale_sales!= .
label var av_wholesale_price "Average Wholesale Price (EIA923) $/kwh " //since 1000/1000 = 1

* Save
save "${wind}/temp/923_resale.dta"  , replace
